package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import ee.maoinvest.backend.database.ConnectionManager;
import ee.maoinvest.backend.model.dao.TempRelationBetweenSubstationAndMainElectricityMeterDAO;
import ee.maoinvest.backend.model.data.TempRelationBetweenSubstationAndMainElectricityMeter;

public class TempRelationBetweenSubstationAndMainElectricityMeterDAOPostgresqlImpl implements TempRelationBetweenSubstationAndMainElectricityMeterDAO
{
	Logger logger = Logger.getLogger(TempRelationBetweenSubstationAndMainElectricityMeterDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public TempRelationBetweenSubstationAndMainElectricityMeterDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	
//-----------------START OF BASIC CRUD-----------------------------------
	@Override
	public int insertTempRelationBetweenSubstationAndMainElectricityMeter(
			TempRelationBetweenSubstationAndMainElectricityMeter tempRelationBetweenSubstationAndMainElectricityMeter) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Temp_relations_between_substation_and_main_electricity_meters " + 
				"(substationFK, mainElectricityMeterFK, dateRelationStarted, dateRelationEnded, " + 
				"electricityMeterDaytimeReadingAtRelationStartDate, electricityMeterNightTimeReadingAtRelationStartDate,electricityMeterReactivePowerReadingAtRelationStartDate, " + 
				"electricityMeterDaytimeReadingAtRelationEndDate, electricityMeterNightTimeReadingAtRelationEndDate, electricityMeterReactivePowerReadingAtRelationEndDate) " + 
				"VALUES " + 
				"(?, ?, ?, ?, " +  
				"?, ?, ?, " + 
				"?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setInt		(	1, tempRelationBetweenSubstationAndMainElectricityMeter.getSubstationFK());
			preparedStatement.setInt		(	2, tempRelationBetweenSubstationAndMainElectricityMeter.getMainElectricityMeterFK());
			preparedStatement.setDate		(	3, tempRelationBetweenSubstationAndMainElectricityMeter.getDateRelationStarted());
			preparedStatement.setObject		(	4, tempRelationBetweenSubstationAndMainElectricityMeter.getDateRelationEnded(), Types.DATE);
			preparedStatement.setBigDecimal	(	5, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterDaytimeReadingAtRelationStartDate());
			preparedStatement.setBigDecimal	(	6, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterNightTimeReadingAtRelationStartDate());
			preparedStatement.setObject		(	7, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterReactivePowerReadingAtRelationStartDate(), Types.NUMERIC);
			preparedStatement.setObject		(	8, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterDaytimeReadingAtRelationEndDate(), Types.NUMERIC);
			preparedStatement.setObject		(	9, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterNightTimeReadingAtRelationEndDate(), Types.NUMERIC);
			preparedStatement.setObject		(	10, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterReactivePowerReadingAtRelationEndDate(), Types.NUMERIC);
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				resultSet = preparedStatement.getGeneratedKeys();
				resultSet.next(); //answer consist only 1 row, so move the cursor just once to start to process the result
				int newId = resultSet.getInt(1); // 1, because identifiers are held in the first column 
				tempRelationBetweenSubstationAndMainElectricityMeter.setTempRelationBetweenSubstationAndMainElectricityMeterID(newId);
				return rowsAffected;
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public TempRelationBetweenSubstationAndMainElectricityMeter getTempRelationBetweenSubstationAndMainElectricityMeterByID(
			int tempRelationBetweenSubstationAndMainElectricityMeterID) 
	{
		resultSet = null;
		TempRelationBetweenSubstationAndMainElectricityMeter tempRelationBetweenSubstationAndMainElectricityMeterRetrieved = null;
		sql = "SELECT * FROM Temp_relations_between_substation_and_main_electricity_meters WHERE tempRelationBetweenSubstationAndMainElectricityMeterID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, tempRelationBetweenSubstationAndMainElectricityMeterID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved = new TempRelationBetweenSubstationAndMainElectricityMeter();
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setTempRelationBetweenSubstationAndMainElectricityMeterID(resultSet.getInt("tempRelationBetweenSubstationAndMainElectricityMeterID"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setSubstationFK(resultSet.getInt("substationFK"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setMainElectricityMeterFK(resultSet.getInt("mainElectricityMeterFK"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setDateRelationStarted(resultSet.getDate("dateRelationStarted"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setDateRelationEnded((Date) resultSet.getObject("dateRelationEnded"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterDaytimeReadingAtRelationStartDate(resultSet.getBigDecimal("electricityMeterDaytimeReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterNightTimeReadingAtRelationStartDate(resultSet.getBigDecimal("electricityMeterNightTimeReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterReactivePowerReadingAtRelationStartDate((BigDecimal) resultSet.getObject("electricityMeterReactivePowerReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterDaytimeReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterDaytimeReadingAtRelationEndDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterNightTimeReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterNightTimeReadingAtRelationEndDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterReactivePowerReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterReactivePowerReadingAtRelationEndDate"));
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return tempRelationBetweenSubstationAndMainElectricityMeterRetrieved;
	}

	@Override
	public int updateTempRelationBetweenSubstationAndMainElectricityMeter(
			TempRelationBetweenSubstationAndMainElectricityMeter tempRelationBetweenSubstationAndMainElectricityMeter) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Temp_relations_between_substation_and_main_electricity_meters " + 
				"SET substationFK = ?, mainElectricityMeterFK = ?, dateRelationStarted = ?, dateRelationEnded = ?, " + 
				"electricityMeterDaytimeReadingAtRelationStartDate = ?, electricityMeterNightTimeReadingAtRelationStartDate = ?,electricityMeterReactivePowerReadingAtRelationStartDate = ?, " + 
				"electricityMeterDaytimeReadingAtRelationEndDate = ?, electricityMeterNightTimeReadingAtRelationEndDate = ?, electricityMeterReactivePowerReadingAtRelationEndDate = ? " + 
				"WHERE " + 
				"tempRelationBetweenSubstationAndMainElectricityMeterID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setInt		(	1, tempRelationBetweenSubstationAndMainElectricityMeter.getSubstationFK());
			preparedStatement.setInt		(	2, tempRelationBetweenSubstationAndMainElectricityMeter.getMainElectricityMeterFK());
			preparedStatement.setDate		(	3, tempRelationBetweenSubstationAndMainElectricityMeter.getDateRelationStarted());
			preparedStatement.setObject		(	4, tempRelationBetweenSubstationAndMainElectricityMeter.getDateRelationEnded(), Types.DATE);
			preparedStatement.setBigDecimal	(	5, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterDaytimeReadingAtRelationStartDate());
			preparedStatement.setBigDecimal	(	6, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterNightTimeReadingAtRelationStartDate());
			preparedStatement.setObject		(	7, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterReactivePowerReadingAtRelationStartDate(), Types.NUMERIC);
			preparedStatement.setObject		(	8, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterDaytimeReadingAtRelationEndDate(), Types.NUMERIC);
			preparedStatement.setObject		(	9, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterNightTimeReadingAtRelationEndDate(), Types.NUMERIC);
			preparedStatement.setObject		(	10, tempRelationBetweenSubstationAndMainElectricityMeter.getElectricityMeterReactivePowerReadingAtRelationEndDate(), Types.NUMERIC);
			preparedStatement.setInt		(	11, tempRelationBetweenSubstationAndMainElectricityMeter.getTempRelationBetweenSubstationAndMainElectricityMeterID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public int deleteTempRelationBetweenSubstationAndMainElectricityMeter(
			int tempRelationBetweenSubstationAndMainElectricityMeterID) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "DELETE FROM Temp_relations_between_substation_and_main_electricity_meters WHERE tempRelationBetweenSubstationAndMainElectricityMeterID = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			preparedStatement.setInt(1, tempRelationBetweenSubstationAndMainElectricityMeterID);
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Deletion successful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.info("Deletion unsuccessful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch(SQLException e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
//-----------------END OF BASIC CRUD-----------------------------------

	@Override
	public List<TempRelationBetweenSubstationAndMainElectricityMeter> getAllTempRelationsBetweenSubstationsAndMainElectricityMeters() 
	{
		List<TempRelationBetweenSubstationAndMainElectricityMeter> listOfTempRelationsBetweenSubstationsAndMainElectricityMeters = 
				new ArrayList<TempRelationBetweenSubstationAndMainElectricityMeter>();
		TempRelationBetweenSubstationAndMainElectricityMeter tempRelationBetweenSubstationAndMainElectricityMeterRetrieved = null;
		sql = "SELECT * FROM Temp_relations_between_substation_and_main_electricity_meters;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved = new TempRelationBetweenSubstationAndMainElectricityMeter();
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setTempRelationBetweenSubstationAndMainElectricityMeterID(resultSet.getInt("tempRelationBetweenSubstationAndMainElectricityMeterID"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setSubstationFK(resultSet.getInt("substationFK"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setMainElectricityMeterFK(resultSet.getInt("mainElectricityMeterFK"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setDateRelationStarted(resultSet.getDate("dateRelationStarted"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setDateRelationEnded((Date) resultSet.getObject("dateRelationEnded"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterDaytimeReadingAtRelationStartDate(resultSet.getBigDecimal("electricityMeterDaytimeReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterNightTimeReadingAtRelationStartDate(resultSet.getBigDecimal("electricityMeterNightTimeReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterReactivePowerReadingAtRelationStartDate((BigDecimal) resultSet.getObject("electricityMeterReactivePowerReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterDaytimeReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterDaytimeReadingAtRelationEndDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterNightTimeReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterNightTimeReadingAtRelationEndDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterReactivePowerReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterReactivePowerReadingAtRelationEndDate"));
			
				listOfTempRelationsBetweenSubstationsAndMainElectricityMeters.add(tempRelationBetweenSubstationAndMainElectricityMeterRetrieved);
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfTempRelationsBetweenSubstationsAndMainElectricityMeters;
	}
	
	@Override
	public TempRelationBetweenSubstationAndMainElectricityMeter getTempRelationBetweenSubstationAndMainElectricityMeterObjectByMainMeterIDAndDateRelationEndedIsNull(
			int substationMainElectricityMeterID) 
	{
		resultSet = null;
		TempRelationBetweenSubstationAndMainElectricityMeter tempRelationBetweenSubstationAndMainElectricityMeterRetrieved = null;
		sql = "SELECT * FROM Temp_relations_between_substation_and_main_electricity_meters WHERE mainElectricityMeterFK = ? " +
				"AND dateRelationEnded IS NULL";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, substationMainElectricityMeterID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved = new TempRelationBetweenSubstationAndMainElectricityMeter();
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setTempRelationBetweenSubstationAndMainElectricityMeterID(resultSet.getInt("tempRelationBetweenSubstationAndMainElectricityMeterID"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setSubstationFK(resultSet.getInt("substationFK"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setMainElectricityMeterFK(resultSet.getInt("mainElectricityMeterFK"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setDateRelationStarted(resultSet.getDate("dateRelationStarted"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setDateRelationEnded((Date) resultSet.getObject("dateRelationEnded"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterDaytimeReadingAtRelationStartDate(resultSet.getBigDecimal("electricityMeterDaytimeReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterNightTimeReadingAtRelationStartDate(resultSet.getBigDecimal("electricityMeterNightTimeReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterReactivePowerReadingAtRelationStartDate((BigDecimal) resultSet.getObject("electricityMeterReactivePowerReadingAtRelationStartDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterDaytimeReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterDaytimeReadingAtRelationEndDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterNightTimeReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterNightTimeReadingAtRelationEndDate"));
				tempRelationBetweenSubstationAndMainElectricityMeterRetrieved.setElectricityMeterReactivePowerReadingAtRelationEndDate((BigDecimal) resultSet.getObject("electricityMeterReactivePowerReadingAtRelationEndDate"));
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return tempRelationBetweenSubstationAndMainElectricityMeterRetrieved;
	}
}
